================================================================================
                    BEFORE vs AFTER - VISUAL DIAGRAM
================================================================================

BEFORE (Causing 500 Error)
================================================================================

[User Login Form]
     |
     | Submits: username="Webster", password="12345678!"
     |
     v
[user_access.php]
     |
     | Executes: SELECT * FROM account WHERE username='Webster' AND upass='...'
     |
     v
[Database - account table]
     |
     | Columns: id, acc_no, uname, upass, email, fname, lname...
     |                              ^^^^^ (NO username column!)
     |
     v
[ERROR!] Column 'username' doesn't exist
     |
     v
[500 Internal Server Error] ❌


AFTER (Fixed)
================================================================================

[User Login Form]
     |
     | Submits: username="Webster", password="12345678!"
     |
     v
[user_access.php] (with SQL injection protection)
     |
     | Executes: SELECT * FROM account WHERE username='Webster' AND upass='...'
     |
     v
[Database - account table]
     |
     | Columns: id, acc_no, uname, username, upass, email, fname, lname...
     |                              ^^^^^ ^^^^^^^^ (BOTH columns exist!)
     |                              |     |
     |                              |     +-- Login username (e.g., 'Webster')
     |                              +-------- Account number (e.g., '9180074482')
     |
     v
[SUCCESS!] User found, credentials match
     |
     v
[Redirect to OTP/PIN verification] ✓


DATABASE STRUCTURE COMPARISON
================================================================================

BEFORE:
+----+----------+-------------+----------+-------+
| id | acc_no   | uname       | upass    | email |
+----+----------+-------------+----------+-------+
| 517| 9180074482| Webster    | 12345678!| ...   |  ← WRONG! uname should be account number
+----+----------+-------------+----------+-------+
                  ^^^^^^^ This was the account number field
                          but had username value!

AFTER:
+----+----------+-------------+----------+----------+-------+
| id | acc_no   | uname       | username | upass    | email |
+----+----------+-------------+----------+----------+-------+
| 517| 9180074482| 9180074482 | Webster  | 12345678!| ...   |  ← CORRECT!
+----+----------+-------------+----------+----------+-------+
                  ^^^^^^^^^^^   ^^^^^^^^
                  Account #     Login username


LOGIN FLOW DIAGRAM
================================================================================

1. User visits login page
   ↓
2. Enters username: "Webster"
   Enters password: "12345678!"
   ↓
3. Form submits to user_access.php
   ↓
4. PHP sanitizes input (prevents SQL injection)
   ↓
5. Query database: WHERE username='Webster' AND upass='12345678!'
   ↓
6. Database finds matching record
   ↓
7. Check account status (ACTIVE/DISABLED/SUSPEND)
   ↓
8. If ACTIVE:
   - Generate OTP code
   - Store in tmp_otp field
   - Send email with OTP
   - Redirect to OTP verification page
   ↓
9. User enters OTP
   ↓
10. If phone_verify=1: Redirect to PIN auth
    Else: Verify OTP and login


FIELD USAGE EXPLANATION
================================================================================

┌─────────────┬──────────────┬─────────────────────────────────────┐
│ Field       │ Example      │ Purpose                             │
├─────────────┼──────────────┼─────────────────────────────────────┤
│ acc_no      │ 9180074482   │ Account number (for display)        │
│ uname       │ 9180074482   │ Account number (internal reference) │
│ username    │ Webster      │ Login username (what user types)    │
│ upass       │ 12345678!    │ Password                            │
│ email       │ user@mail.com│ Email address                       │
│ fname       │ Chibuogwu    │ First name                          │
│ lname       │ Nnabuife     │ Last name                           │
│ tmp_otp     │ 236991       │ Temporary OTP code                  │
│ phone_verify│ 1            │ Phone verified? (1=yes, 0=no)       │
│ status      │ ACTIVE       │ Account status                      │
└─────────────┴──────────────┴─────────────────────────────────────┘


SQL INJECTION PROTECTION
================================================================================

BEFORE (Vulnerable):
$username = $_POST['username'];  // Direct use - DANGEROUS!
$query = "SELECT * FROM account WHERE username='$username'";

If hacker enters: ' OR '1'='1
Query becomes: SELECT * FROM account WHERE username='' OR '1'='1'
Result: Returns ALL accounts! 🚨

AFTER (Protected):
$username = mysqli_real_escape_string($connection, $_POST['username']);
$query = "SELECT * FROM account WHERE username='$username'";

If hacker enters: ' OR '1'='1
Query becomes: SELECT * FROM account WHERE username='\' OR \'1\'=\'1'
Result: No match found, login fails ✓


ERROR HANDLING IMPROVEMENT
================================================================================

BEFORE:
$result = mysqli_query($connection, $query) or die(mysqli_error($connection));
                                               ^^^^ Shows error to user
                                                    Causes 500 error
                                                    Exposes database structure

AFTER:
$result = mysqli_query($connection, $query);
if (!$result) {
    error_log("Database error: " . mysqli_error($connection));  // Log for admin
    $msg = "System error. Please try again.";  // Generic message for user
}


MIGRATION SCRIPT EXPLANATION
================================================================================

Step 1: Add column
ALTER TABLE `account` ADD COLUMN `username` VARCHAR(100) AFTER `uname`;
        ^^^^^^^^       ^^^^^^^^^^           ^^^^^^^^^^^^       ^^^^^^^^
        Table name     Add new column       Data type          Position

Step 2: Populate data
UPDATE `account` SET `username` = `uname` WHERE `username` IS NULL;
       ^^^^^^^^      ^^^^^^^^^^^   ^^^^^^       ^^^^^^^^^^^^^^^^^^^
       Table         Set username  From uname   Only if empty

Step 3: Fix test account
UPDATE `account` SET `username` = 'Webster', `uname` = '9180074482' WHERE `id` = 517;
                     ^^^^^^^^^^^   ^^^^^^^^^  ^^^^^^   ^^^^^^^^^^^^^
                     Login name    Account #  Correct  Specific record

Step 4: Add index (for speed)
CREATE INDEX `idx_username` ON `account`(`username`);
             ^^^^^^^^^^^^^^    ^^^^^^^^^ ^^^^^^^^^^
             Index name        Table     Column to index


VERIFICATION CHECKLIST
================================================================================

✓ Database has username column
✓ All accounts have username populated
✓ No duplicate usernames
✓ Login query works without errors
✓ User class loads successfully
✓ Can login with username and password
✓ OTP/PIN flow works after login
✓ No 500 errors on login page


COMMON MISTAKES TO AVOID
================================================================================

❌ DON'T: Use account number to login (old way)
✓ DO: Use username to login (new way)

❌ DON'T: Set username = fname (first name)
✓ DO: Set username = unique identifier

❌ DON'T: Delete uname column
✓ DO: Keep both uname (account #) and username (login)

❌ DON'T: Make username same as password
✓ DO: Use different values for security

❌ DON'T: Leave username NULL
✓ DO: Populate all usernames before going live


TESTING SCENARIOS
================================================================================

Test 1: Valid Login
Input: username="Webster", password="12345678!"
Expected: Redirect to OTP page ✓

Test 2: Invalid Username
Input: username="InvalidUser", password="12345678!"
Expected: "Invalid Username or Password" message ✓

Test 3: Invalid Password
Input: username="Webster", password="wrongpass"
Expected: "Invalid Username or Password" message ✓

Test 4: Disabled Account
Input: Valid credentials for disabled account
Expected: "Account has been disabled" message ✓

Test 5: Suspended Account
Input: Valid credentials for suspended account
Expected: "Account is suspended" message ✓


ROLLBACK PLAN (If Something Goes Wrong)
================================================================================

If you need to undo the changes:

1. Restore database backup:
   mysql -u username -p database_name < backup_before_fix.sql

2. Revert user_access.php:
   - Replace with backup copy
   - Or manually undo changes

3. Check error logs:
   - Look for specific error messages
   - Fix issues one by one

4. Contact support if needed


SUCCESS INDICATORS
================================================================================

You'll know the fix worked when:

✓ Login page loads without 500 error
✓ Can enter username and password
✓ Form submits successfully
✓ Redirects to OTP/PIN page
✓ No error messages in browser console
✓ No errors in PHP error log
✓ verify_fix.php shows all green checkmarks


================================================================================
                            END OF DIAGRAM
================================================================================
